﻿'Description:物料提请配送相关
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:longb
'create Date:2022-06-01
'Modify by longb
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient

Public Class SendRequestBusiness
    Private m_ContainerObj As New uLMSContainerBusiness
    ''' <summary>
    ''' add by longb 2022-06-01
    ''' 根据工序ID和任务单号，获取工序大小件信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetBigSmallData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerID")) '判断是初装（1）、检装（2） add by longb 2023-01-16
        Dim qty As String = "pm.firstassembleqty"
        If strFirstSecondIssue = "2" Then
            qty = "pm.checkassembleqty "
        End If
        Dim sqlStr As String = <string>
                SELECT  ep.ENGINEPARTPLANID,im.issueerpmaterialid, cm.productname,p.description productdesc,cm.productbatchno,{0} firstassembleqty,im.issueqty,nvl(SUM(imc.confirmqty),0) totalCollectQty 
                 ,DECODE(pm.PARTTYPE,0,'大件','小件') PARTTYPE,im.workflowstepid,wfs.workflowstepname,DECODE(itr.ISSUEERPMATERIALID,NULL,'未发送','已发送') STATUS,pm.productid
                FROM issueerpmaterial im 
                LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid 
                LEFT JOIN workflowstep wfs ON im.workflowstepid=wfs.workflowstepid 
                LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid 
                LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid 
                LEFT JOIN product p ON pm.productid=p.productid 
                LEFT JOIN (select * from IssueERPMaterialConfirmation where mfgorderid='{1}') imc ON im.issueerpmaterialid=imc.issueerpmaterialid 
                left join container c ON c.containerid=im.containerid  
                LEFT JOIN mfgorder mfg ON mfg.mfgorderid=c.mfgorderid   
                LEFT JOIN Userattribute u ON mfg.mfgorderid=u.parentid 
                LEFT JOIN IssueMaterialTransport itr ON itr.ISSUEERPMATERIALID =im.ISSUEERPMATERIALID AND itr.ENGINEPARTPLANID=ep.ENGINEPARTPLANID--所需物料配送表
                where im.issueqty>0 and  c.status='1' and u.userattributename='发动机'               
            </string>.Value
        Dim sb As New StringBuilder
        sb.AppendLine(String.Format(sqlStr, qty, para("mfgorderid"))) 'moify by longb 2023-01-16
        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and im.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        'If para.ContainsKey("Machine") Then
        '    If Not String.IsNullOrWhiteSpace(para("Machine")) Then
        '        sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
        '    End If
        'End If
        If para.ContainsKey("workflowstepid") Then
            If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                sb.AppendFormat(" and im.workflowstepid in ('{0}') ", para("workflowstepid").Replace(",", "','"))
            End If
        End If
        If strFirstSecondIssue = "2" Then 'add by longb 检装阶段只显示大于0 的检装大小件 add by longb 2023-01-16
            sb.AppendLine(" AND nvl(pm.checkassembleqty,0)>0")
        End If
        sb.AppendFormat("GROUP BY ep.ENGINEPARTPLANID, im.issueerpmaterialid,cm.productname,cm.productbatchno,{0},im.issueqty ,p.description,pm.PARTTYPE ,im.workflowstepid, wfs.workflowstepname,itr.ISSUEERPMATERIALID,pm.productid ", qty) 'moify by longb 2023-01-16
        sb.AppendLine("Order by  cm.productname,cm.productbatchno asc")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 开工 ----更新4【批次工序派工】表 开工时间，开工人
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateOpenWork(ByVal para As Dictionary(Of String, String)) As Integer
        Dim sb As New StringBuilder
        sb.AppendFormat(" UPDATE ContainerStepDispatch SET StartWorkDate=sysdate,StartWorkEmpID='{0}' WHERE WORKFLOWSTEPID in ('{1}')", para("eplID"), para("workflowstepID").Replace(",", "','"))
        Return DatabaseHelper.ExecuteSql(sb.ToString)
    End Function
    ''' <summary>
    ''' 获取物料配送管理任务单
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCollectContainerNo(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                  SELECT DISTINCT * FROM (
                SELECT u.attributevalue,mfg.mfgordername,ep.containerno togetherflag,decode(WF.WORKFLOWTYPE,'施工工艺','临时','技通工艺','临时','正常') WORKFLOWTYPE
                ,pb.productname,p.description, w.workflowstepname STEPNAME,wfs.workflowstepname,wf.description wfdesc,ep.containerno,
                to_char(ep.plannedstartdate,'yyyy-MM-dd HH:mi') plannedstartdate, to_char(ep.plannedfinishdate,'yyyy-MM-dd HH:mi') plannedfinishdate
                ,NVL(ep.containerid,ep.enginecontainerid) containerid,ep.containername,imt.WORKFLOWSTEPID,ep.ENGINEPARTPLANID
                ,decode(imt.STATUS,0,'待接收',5,'已接收',10,'配送中',15,'配送完',20,'收料完成','')STATUS,DECODE(imt.ApplyType,1,'正常','临时') ApplyType
                FROM IssueMaterialTransport imt
                LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =imt.WORKFLOWSTEPID
                LEFT JOIN enginepartplan ep ON ep.ENGINEPARTPLANID =imt.ENGINEPARTPLANID 
                INNER JOIN mfgorder mfg ON mfg.mfgorderid=ep.mfgorderid 
                LEFT JOIN Userattribute u ON u.parentid=mfg.mfgorderid AND u.userattributename='发动机'
                LEFT JOIN workflow wf ON wf.workflowid=ep.workflowid
                left join workflowbase wfb on wfb.workflowbaseid=wf.workflowbaseid
                LEFT JOIN product p ON ep.productid=p.productid
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                INNER JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid WHERE 1=1
                      </string>)

        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and ep.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        If para.ContainsKey("Machine") Then
            If Not String.IsNullOrWhiteSpace(para("Machine")) Then
                sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
            End If
        End If

        If para.ContainsKey("MfgOrdername") Then
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and mfg.mfgordername like '%{0}%' ", para("MfgOrdername"))
            End If
        End If

        If para.ContainsKey("WorkFlowType") Then
            If Not String.IsNullOrWhiteSpace(para("WorkFlowType")) Then
                sb.AppendFormat(" and decode(WF.WORKFLOWTYPE,'施工工艺','0','技通工艺','0','1')='{0}' ", para("WorkFlowType"))
            End If
        End If

        If para.ContainsKey("Status") Then
            If Not String.IsNullOrWhiteSpace(para("Status")) Then
                sb.AppendFormat(" and imt.STATUS='{0}' ", para("Status"))
            End If
        End If
        If para.ContainsKey("ApplyType") Then
            If Not String.IsNullOrWhiteSpace(para("ApplyType")) Then
                sb.AppendFormat(" and imt.ApplyType='{0}' ", para("ApplyType"))
            End If
        End If
        sb.AppendLine(")Order by plannedfinishdate,mfgordername,productname ASC")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 获取物料配送大小件信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetApplyBigSmallData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sqlStr As String = <string>
                SELECT * FROM (SELECT  irt.ISSUEMATERIALTRANSPORTID,ep.ENGINEPARTPLANID,im.issueerpmaterialid, cm.productname,p.description productdesc,cm.productbatchno,pm.firstassembleqty,im.issueqty 
                 ,DECODE(pm.PARTTYPE,0,'大件','小件') PARTTYPE,ep.WORKFLOWSTEPID, irt.APPLYTYPE,irt.productid,irt.APPLYQTY,NVL(irtd.SendQTY,0) SendQTY,(nvl(imcc.CONFIRMQTY,0)-NVL(irtd.SendQTY,0)) EditorQTY
                ,e.FULLNAME,to_char(irt.CREATEDATE,'yyyy-MM-dd hh24:mm:ss') CREATEDATE,decode(irt.STATUS,0,'待接收',5,'已接收',10,'配送中',15,'配送完',20,'收料完成',25,'待提交','')STATUS,irt.PURPOSE
                ,nvl(imcc.CONFIRMQTY,0) IssTotal
                FROM issueerpmaterial im 
                LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid 
                LEFT JOIN workflowstep wfs ON im.workflowstepid=wfs.workflowstepid 
                LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid 
                LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid 
                LEFT JOIN product p ON pm.productid=p.productid 
                left join container c ON c.containerid=im.containerid  
                LEFT JOIN mfgorder mfg ON mfg.mfgorderid=c.mfgorderid   
                LEFT JOIN Userattribute u ON mfg.mfgorderid=u.parentid 
                LEFT JOIN IssueMaterialTransport irt ON irt.ENGINEPARTPLANID =ep.ENGINEPARTPLANID AND irt.ISSUEERPMATERIALID =im.ISSUEERPMATERIALID
                LEFT JOIN (SELECT SUM(CONFIRMQTY) SendQTY,ISSUEMATERIALTRANSPORTID FROM IssueMaterialTransportDetail GROUP BY ISSUEMATERIALTRANSPORTID) irtd ON irtd.ISSUEMATERIALTRANSPORTID=irt.ISSUEMATERIALTRANSPORTID
              	LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =irt.CREATEEMPLOYEEID 
                LEFT JOIN ( SELECT iss.PARTMENUINFOID,SUM(con.confirmqty) confirmqty FROM issueerpmaterial iss LEFT JOIN IssueERPMaterialConfirmation con ON con.ISSUEERPMATERIALID =iss.ISSUEERPMATERIALID GROUP BY iss.PARTMENUINFOID ) imcc ON imcc.PARTMENUINFOID=im.PARTMENUINFOID
                where im.issueqty>0 and  c.status='1' and u.userattributename='发动机' AND irt.APPLYTYPE ='1'             
            </string>.Value
        Dim sb As New StringBuilder
        sb.AppendLine(sqlStr)
        sb.AppendLine(" AND NVL(irt.APPLYQTY,0) <>0")
        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and im.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        If para.ContainsKey("workflowstepid") Then
            If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                sb.AppendFormat(" and im.workflowstepid ='{0}' ", para("workflowstepid"))
            End If
        End If
        sb.AppendLine(<string>
                          UNION ALL SELECT irt.ISSUEMATERIALTRANSPORTID,ep.ENGINEPARTPLANID,''issueerpmaterialid,irt.productname,p.description productdesc,irt.productbatchno
                            ,0 firstassembleqty,0 issueqty,''PARTTYPE,irt.WORKFLOWSTEPID,irt.APPLYTYPE,irt.productid,
                            irt.APPLYQTY,NVL(irtd.SendQTY,0) SendQTY,(irt.APPLYQTY-NVL(irtd.SendQTY,0)) EditorQTY
                            ,e.FULLNAME,to_char(irt.CREATEDATE,'yyyy-MM-dd hh24:mm:ss') CREATEDATE,decode(irt.STATUS,0,'待接收',5,'已接收',10,'配送中',15,'配送完',20,'收料完成',25,'待提交','')STATUS,irt.PURPOSE,0 IssTotal
                            FROM IssueMaterialTransport irt
                            LEFT JOIN (SELECT SUM(CONFIRMQTY) SendQTY,ISSUEMATERIALTRANSPORTID FROM IssueMaterialTransportDetail GROUP BY ISSUEMATERIALTRANSPORTID) irtd ON irtd.ISSUEMATERIALTRANSPORTID=irt.ISSUEMATERIALTRANSPORTID
                            LEFT JOIN enginepartplan ep ON ep.ENGINEPARTPLANID=irt.ENGINEPARTPLANID
                            LEFT JOIN product p ON irt.productid=p.productid
                            LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =irt.CREATEEMPLOYEEID 
                            WHERE  irt.APPLYTYPE ='2' 
                      </string>)
        sb.AppendLine("  AND NVL(irt.APPLYQTY,0) <>0")
        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and ep.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        If para.ContainsKey("workflowstepid") Then
            If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                sb.AppendFormat(" and irt.workflowstepid ='{0}' ", para("workflowstepid"))
            End If
        End If
        sb.AppendLine(" )Order by  productname,productbatchno asc")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 根据工序ID和任务单号、判断大小件信息是否已配送完
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckApplyBigSmallSended(ByVal para As Dictionary(Of String, String)) As Boolean
        CheckApplyBigSmallSended = False
        Dim sqlStr As String = <string>
                SELECT  irt.ISSUEMATERIALTRANSPORTID,ep.ENGINEPARTPLANID,im.issueerpmaterialid, cm.productname,p.description productdesc,cm.productbatchno,pm.firstassembleqty,im.issueqty 
                 ,DECODE(pm.PARTTYPE,0,'大件','小件') PARTTYPE,ep.WORKFLOWSTEPID ,irt.APPLYQTY,NVL(irtd.SendQTY,0) SendQTY,(irt.APPLYQTY-NVL(irtd.SendQTY,0)) EditorQTY
                FROM issueerpmaterial im 
                LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid 
                LEFT JOIN workflowstep wfs ON im.workflowstepid=wfs.workflowstepid 
                LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid 
                LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid 
                LEFT JOIN product p ON pm.productid=p.productid 
                left join container c ON c.containerid=im.containerid  
                LEFT JOIN mfgorder mfg ON mfg.mfgorderid=c.mfgorderid   
                LEFT JOIN Userattribute u ON mfg.mfgorderid=u.parentid 
                LEFT JOIN IssueMaterialTransport irt ON irt.ENGINEPARTPLANID =ep.ENGINEPARTPLANID AND irt.ISSUEERPMATERIALID =im.ISSUEERPMATERIALID
                LEFT JOIN (SELECT SUM(CONFIRMQTY) SendQTY,ISSUEMATERIALTRANSPORTID FROM IssueMaterialTransportDetail GROUP BY ISSUEMATERIALTRANSPORTID) irtd ON irtd.ISSUEMATERIALTRANSPORTID=irt.ISSUEMATERIALTRANSPORTID
              	where im.issueqty>0 and  c.status='1' and u.userattributename='发动机' AND irt.APPLYTYPE ='1' AND (irt.APPLYQTY-NVL(irtd.SendQTY,0))>0             
            </string>.Value
        Dim sb As New StringBuilder
        sb.AppendLine(sqlStr)
        sb.AppendLine(" AND NVL(irt.APPLYQTY,0) <>0")
        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and im.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        If para.ContainsKey("workflowstepid") Then
            If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                sb.AppendFormat(" and im.workflowstepid ='{0}' ", para("workflowstepid"))
            End If
        End If
        sb.AppendLine("GROUP BY irt.ISSUEMATERIALTRANSPORTID,ep.ENGINEPARTPLANID, im.issueerpmaterialid,cm.productname,cm.productbatchno,pm.firstassembleqty,im.issueqty ,p.description,pm.PARTTYPE,ep.WORKFLOWSTEPID")
        sb.AppendLine(",irt.APPLYQTY ,irtd.SendQTY Order by  cm.productname,cm.productbatchno asc")
        If para.ContainsKey("APPLYTYPE") And para("APPLYTYPE") = "2" Then
            sb = New StringBuilder()
            sb.AppendLine(<string>
                          SELECT irt.ISSUEMATERIALTRANSPORTID,ep.ENGINEPARTPLANID,''issueerpmaterialid,irt.productname,p.description productdesc,irt.productbatchno
                            ,0 firstassembleqty,0 issueqty,''PARTTYPE,irt.WORKFLOWSTEPID,irt.APPLYTYPE,irt.productid,
                            irt.APPLYQTY,NVL(irtd.SendQTY,0) SendQTY,(irt.APPLYQTY-NVL(irtd.SendQTY,0)) EditorQTY
                            FROM IssueMaterialTransport irt
                            LEFT JOIN (SELECT SUM(CONFIRMQTY) SendQTY,ISSUEMATERIALTRANSPORTID FROM IssueMaterialTransportDetail GROUP BY ISSUEMATERIALTRANSPORTID) irtd ON irtd.ISSUEMATERIALTRANSPORTID=irt.ISSUEMATERIALTRANSPORTID
                            LEFT JOIN enginepartplan ep ON ep.ENGINEPARTPLANID=irt.ENGINEPARTPLANID
                            LEFT JOIN product p ON irt.productid=p.productid
                            WHERE  irt.APPLYTYPE ='2' AND (irt.APPLYQTY-NVL(irtd.SendQTY,0))>0
                      </string>)
            sb.AppendLine(" AND NVL(irt.APPLYQTY,0) <>0")
            If para.ContainsKey("ContainerNo") Then
                If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                    sb.AppendFormat(" and ep.ContainerNo like '%{0}%' ", para("ContainerNo"))
                End If
            End If
            If para.ContainsKey("workflowstepid") Then
                If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                    sb.AppendFormat(" and irt.workflowstepid ='{0}' ", para("workflowstepid"))
                End If
            End If
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Dim Status As String = "15"
        If (dt.Rows.Count > 0) Then
            Status = "10"
        End If
        sb = New StringBuilder()
        sb.AppendFormat(" UPDATE IssueMaterialTransport SET Status='{0}' WHERE EnginepartplanID='{1}' AND APPLYTYPE='{2}' AND WORKFLOWSTEPID='{3}'", Status, para("EnginepartplanID"), para("APPLYTYPE"), para("workflowstepid"))
        If DatabaseHelper.ExecuteSql(sb.ToString) > 0 Then
            CheckApplyBigSmallSended = True
        End If
    End Function

    ''' <summary>
    ''' 根据序ID和任务单号、获取配送信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetBigSmallConfirmData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerID")) '判断是初装（1）、检装（2） add by longb 2023-01-16
        Dim qty As String = "pm.firstassembleqty"
        If strFirstSecondIssue = "2" Then
            qty = "pm.checkassembleqty "
        End If
        Dim sqlStr As String = <string>
                SELECT  irt.ISSUEMATERIALTRANSPORTID,ep.ENGINEPARTPLANID,im.issueerpmaterialid, cm.productname,p.description productdesc,cm.productbatchno,{0} firstassembleqty,im.issueqty 
                 ,DECODE(pm.PARTTYPE,0,'大件','小件') PARTTYPE,ep.WORKFLOWSTEPID ,irt.APPLYQTY,NVL(irtd.SendQTY,0) SendQTY,(irt.APPLYQTY-NVL(irtd.SendQTY,0)) EditorQTY
                ,nvl(irev.RECEIVEQTY,0) RECEIVEQTY,(NVL(irtd.SendQTY,0)-nvl(irev.RECEIVEQTY,0)) NORECEIVEQTY,(NVL(irtd.SendQTY,0)-nvl(irev.RECEIVEQTY,0))NNORECEIVEQTY
                FROM issueerpmaterial im 
                LEFT JOIN enginepartplan ep ON im.containerid=ep.containerid 
                LEFT JOIN workflowstep wfs ON im.workflowstepid=wfs.workflowstepid 
                LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid 
                LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid 
                LEFT JOIN product p ON pm.productid=p.productid 
                left join container c ON c.containerid=im.containerid  
                LEFT JOIN mfgorder mfg ON mfg.mfgorderid=c.mfgorderid   
                LEFT JOIN Userattribute u ON mfg.mfgorderid=u.parentid 
                LEFT JOIN IssueMaterialTransport irt ON irt.ENGINEPARTPLANID =ep.ENGINEPARTPLANID AND irt.ISSUEERPMATERIALID =im.ISSUEERPMATERIALID
                LEFT JOIN (SELECT SUM(CONFIRMQTY) SendQTY,ISSUEMATERIALTRANSPORTID FROM IssueMaterialTransportDetail GROUP BY ISSUEMATERIALTRANSPORTID) irtd ON irtd.ISSUEMATERIALTRANSPORTID=irt.ISSUEMATERIALTRANSPORTID
              	LEFT JOIN (SELECT SUM(RECEIVEQTY) RECEIVEQTY,ISSUEMATERIALTRANSPORTID FROM IssueMaterialReceiveDetail GROUP BY ISSUEMATERIALTRANSPORTID) irev ON irev.ISSUEMATERIALTRANSPORTID=irt.ISSUEMATERIALTRANSPORTID
                where im.issueqty>0 and  c.status='1' and u.userattributename='发动机'                 
            </string>.Value
        Dim sb As New StringBuilder
        sb.AppendLine(String.Format(sqlStr, qty)) 'modify by longb 2023-01-16
        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and im.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        'If para.ContainsKey("Machine") Then
        '    If Not String.IsNullOrWhiteSpace(para("Machine")) Then
        '        sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
        '    End If
        'End If
        If para.ContainsKey("workflowstepid") Then
            If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                sb.AppendFormat(" and im.workflowstepid ='{0}' ", para("workflowstepid"))
            End If
        End If
        If strFirstSecondIssue = "2" Then 'add by longb 检装阶段只显示大于0 的检装大小件 add by longb 2023-01-16
            sb.AppendLine(" AND nvl(pm.checkassembleqty,0)>0")
        End If
        sb.AppendFormat("GROUP BY irt.ISSUEMATERIALTRANSPORTID,ep.ENGINEPARTPLANID, im.issueerpmaterialid,cm.productname,cm.productbatchno,{0},im.issueqty ,p.description,pm.PARTTYPE,ep.WORKFLOWSTEPID ", qty)
        sb.AppendLine(",irt.APPLYQTY ,irtd.SendQTY,irev.RECEIVEQTY Order by  cm.productname,cm.productbatchno asc")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 根据条件查询--大小零件信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetAllProduct(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                    SELECT DISTINCT p.PRODUCTID ID,p.DESCRIPTION,pm.BIGSMALLPART,p2.PRODUCTNAME||':'||p.PRODUCTREVISION NAME,p2.PRODUCTNAME FROM PRODUCT p
                    LEFT JOIN PRODUCTBASE p2 ON p2.PRODUCTBASEID =p.PRODUCTBASEID 
                    LEFT JOIN productmateriallistitem pm ON pm.PRODUCTID =p.PRODUCTID 
                    WHERE pm.BIGSMALLPART LIKE '%零件%'
                      </string>.Value)
        If para.ContainsKey("Name") Then
            If Not String.IsNullOrWhiteSpace(para("Name")) Then
                sb.AppendFormat(" and p2.PRODUCTNAME LIKE '%{0}%' ", para("Name"))
            End If
        End If
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 批量删除临时物料提请
    ''' </summary>
    ''' <param name="IDS"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function DeleteTempData(ByVal IDS As String, ByVal TIDS As String) As Boolean
        Dim arr As New ArrayList()
        Dim sb As New StringBuilder
        sb.AppendFormat(" DELETE IssueMaterialTransport WHERE APPLYTYPE ='2' AND STATUS='25' AND ISSUEMATERIALTRANSPORTID IN ('{0}')", IDS.Replace(",", "','"))
        arr.Add(sb.ToString)
        sb = New StringBuilder
        sb.AppendFormat(" DELETE ZsRequiredToolsTransport WHERE APPLYTYPE ='2' AND STATUS='25' AND ZSREQUIREDTOOLSTRANSPORTID IN ('{0}')", TIDS.Replace(",", "','"))
        arr.Add(sb.ToString)
        sb = New StringBuilder
        sb.AppendFormat(" DELETE ZsRTTransportChild WHERE  ZSREQUIREDTOOLSTRANSPORTID IN ('{0}')", TIDS.Replace(",", "','"))
        arr.Add(sb.ToString)
        Return DatabaseHelper.ExecuteSqlListTran(arr)
    End Function
    ''' <summary>
    ''' 批量提交--更新提交状态到待接收状态
    ''' </summary>
    ''' <param name="IDS"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateTempStatus(ByVal IDS As String, ByVal TIDS As String) As Boolean
        Dim arr As New ArrayList()
        Dim sb As New StringBuilder
        sb.AppendFormat(" Update IssueMaterialTransport SET STATUS='0' WHERE APPLYTYPE ='2' AND STATUS='25' AND ISSUEMATERIALTRANSPORTID IN ('{0}')", IDS.Replace(",", "','"))
        arr.Add(sb.ToString)
        sb = New StringBuilder
        sb.AppendFormat(" Update ZsRequiredToolsTransport SET STATUS='0' WHERE APPLYTYPE ='2' AND STATUS='25' AND ZSREQUIREDTOOLSTRANSPORTID IN ('{0}')", TIDS.Replace(",", "','"))
        arr.Add(sb.ToString)
        Return DatabaseHelper.ExecuteSqlListTran(arr)
    End Function
    ''' <summary>
    ''' 批量提交--更新配送完状态到收料完成状态
    ''' </summary>
    ''' <param name="IDS"></param>
    ''' <param name="TIDS"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateTempReceiveStatus(ByVal IDS As String, ByVal TIDS As String) As Boolean
        Dim arr As New ArrayList()
        Dim sb As New StringBuilder
        sb.AppendFormat(" Update IssueMaterialTransport SET STATUS='20' WHERE APPLYTYPE ='2' AND STATUS='15' AND ISSUEMATERIALTRANSPORTID IN ('{0}')", IDS.Replace(",", "','"))
        arr.Add(sb.ToString)
        sb = New StringBuilder
        sb.AppendFormat(" Update ZsRequiredToolsTransport SET STATUS='20' WHERE APPLYTYPE ='2' AND STATUS='15' AND ZSREQUIREDTOOLSTRANSPORTID IN ('{0}')", TIDS.Replace(",", "','"))
        arr.Add(sb.ToString)
        Return DatabaseHelper.ExecuteSqlListTran(arr)
    End Function

    '-------------------工装工具配送-------------------------
    ''' <summary>
    ''' 根据条件查询--工装工具信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetAllTools(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendFormat("SELECT CUTTOOLID ID,CUTTOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION FROM CUTTOOL  WHERE CUTTOOLNAME LIKE '%{0}%' OR DESCRIPTION LIKE '%{0}%'", para("Name"))
        sb.AppendFormat(" UNION ALL SELECT SPECIALTOOLID ID,SPECIALTOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION FROM SPECIALTOOL  WHERE SPECIALTOOLNAME LIKE '%{0}%' OR DESCRIPTION LIKE '%{0}%'", para("Name"))
        sb.AppendFormat(" UNION ALL SELECT MEASURETOOLID ID,MEASURETOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION FROM MEASURETOOL   WHERE MEASURETOOLNAME LIKE '%{0}%' OR DESCRIPTION LIKE '%{0}%'", para("Name"))
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询工装工具任务
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetZsRequiredTools(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        If para.ContainsKey("TransportType") Then
            If para("TransportType") = "3" Then
                sb.AppendLine("SELECT DISTINCT * FROM (SELECT imt.ZSREQUIREDTOOLSTRANSPORTID,")
            Else
                sb.AppendLine("SELECT DISTINCT * FROM (SELECT ")
            End If
        End If
        sb.AppendLine(<string>
                  u.attributevalue,mfg.mfgordername,ep.containerno togetherflag,decode(WF.WORKFLOWTYPE,'施工工艺','临时','技通工艺','临时','正常') WORKFLOWTYPE
                ,pb.productname,p.description, w.workflowstepname STEPNAME,wfs.workflowstepname,wf.description wfdesc,ep.containerno,
                to_char(ep.plannedstartdate,'yyyy-MM-dd hh24:mi') plannedstartdate, to_char(ep.plannedfinishdate,'yyyy-MM-dd hh24:mi') plannedfinishdate
                ,NVL(ep.containerid,ep.enginecontainerid) containerid,ep.containername,imt.WORKFLOWSTEPID,ep.ENGINEPARTPLANID
                ,decode(imt.STATUS,0,'待接收',5,'已接收',10,'配送中',15,'配送完',20,'收料完成','')STATUS,DECODE(imt.ApplyType,1,'正常','临时') ApplyType
                FROM ZsRequiredToolsTransport imt
                LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =imt.WORKFLOWSTEPID
                LEFT JOIN enginepartplan ep ON ep.ENGINEPARTPLANID =imt.ENGINEPARTPLANID 
                LEFT JOIN mfgorder mfg ON mfg.mfgorderid=ep.mfgorderid 
                LEFT JOIN Userattribute u ON u.parentid=mfg.mfgorderid AND u.userattributename='发动机'
                LEFT JOIN workflow wf ON wf.workflowid=ep.workflowid
                LEFT JOIN workflowbase wfb on wfb.workflowbaseid=wf.workflowbaseid
                LEFT JOIN product p ON ep.productid=p.productid
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid WHERE 1=1
                      </string>)

        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and ep.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If
        If para.ContainsKey("Machine") Then
            If Not String.IsNullOrWhiteSpace(para("Machine")) Then
                sb.AppendFormat(" and u.attributevalue like '%{0}%' ", para("Machine"))
            End If
        End If

        If para.ContainsKey("MfgOrdername") Then
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and mfg.mfgordername like '%{0}%' ", para("MfgOrdername"))
            End If
        End If

        If para.ContainsKey("WorkFlowType") Then
            If Not String.IsNullOrWhiteSpace(para("WorkFlowType")) Then
                sb.AppendFormat(" and decode(WF.WORKFLOWTYPE,'施工工艺','0','技通工艺','0','1')='{0}' ", para("WorkFlowType"))
            End If
        End If

        If para.ContainsKey("Status") Then
            If Not String.IsNullOrWhiteSpace(para("Status")) Then
                sb.AppendFormat(" and imt.STATUS='{0}' ", para("Status"))
            End If
        End If
        If para.ContainsKey("ApplyType") Then
            If Not String.IsNullOrWhiteSpace(para("ApplyType")) Then
                sb.AppendFormat(" and imt.ApplyType='{0}' ", para("ApplyType"))
            End If
        End If
        If para.ContainsKey("TransportType") Then
            If Not String.IsNullOrWhiteSpace(para("TransportType")) Then
                sb.AppendFormat(" and imt.TransportType='{0}' ", para("TransportType"))
            End If
        End If
        sb.AppendLine(")Order by plannedfinishdate,mfgordername,productname ASC")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 查询工装工具--申请信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetZsRequiredToolsTransport(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder()
        sb.AppendLine(<string>
                        SELECT rt.ZSREQUIREDTOOLSTRANSPORTID,rt.PRODUCTBATCHNO,to_Char(rt.CREATEDATE,'yyyy-MM-dd hh24:mm:ss') CREATEDATE,e.FULLNAME
                        ,tc.ZSRTTRANSPORTCHILDID,tc.QTY,decode(rt.STATUS,0,'待接收',5,'已接收',10,'配送中',15,'配送完',20,'收料完成','25','待提交','')STATUS,DECODE(rt.ApplyType,1,'正常','临时') ApplyType
                        ,rt.PURPOSE,DECODE(cl.CUTTOOLNAME,NULL,DECODE(s.SPECIALTOOLNAME,NULL,m.MEASURETOOLNAME,s.SPECIALTOOLNAME),cl.CUTTOOLNAME) TOOlNAME
                        ,DECODE(cl.DRAWINGNO,NULL,DECODE(s.DRAWINGNO,NULL,m.DRAWINGNO,s.DRAWINGNO),cl.DRAWINGNO) DRAWINGNO
                        ,DECODE(cl.DESCRIPTION ,NULL,DECODE(s.DESCRIPTION,NULL,m.DESCRIPTION,s.DESCRIPTION),cl.DESCRIPTION) DESCRIPTION
                        ,DECODE(cl.SPECIFICATION,NULL,DECODE(s.SPECIFICATION,NULL,m.SPECIFICATION,s.SPECIFICATION),cl.SPECIFICATION) SPECIFICATION
                        ,DECODE(cl.CUTTOOLNAME,NULL,DECODE(s.SPECIALTOOLNAME,NULL,'夹模具','量具'),'刀具') TOOlTYPE
                        ,tcd.CONFIRMQTY SENDQTY,NVL(tc.QTY,0)-NVL(tcd.CONFIRMQTY,0) EDITORQTY,rt.PRODUCTBATCHNO,rt.PREPAREQTY,to_char(rt.OUTSTORETIME,'yyyy-MM-dd hh24:mi') OUTSTORETIME 
                        FROM ZsRequiredToolsTransport rt
                        LEFT JOIN ZsRTTransportChild tc ON tc.ZSREQUIREDTOOLSTRANSPORTID =rt.ZSREQUIREDTOOLSTRANSPORTID
                        LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =rt.CREATEEMPLOYEEID 
                        LEFT JOIN CUTTOOL cl ON cl.CUTTOOLID =tc.TOOLID 
                        LEFT JOIN SPECIALTOOL s ON s.SPECIALTOOLID =tc.TOOLID
                        LEFT JOIN MEASURETOOL m ON m.MEASURETOOLID=tc.TOOLID
                        LEFT JOIN ENGINEPARTPLAN ep ON ep.ENGINEPARTPLANID =rt.ENGINEPARTPLANID
                        LEFT JOIN (SELECT SUM(CONFIRMQTY) CONFIRMQTY,ZSRTTRANSPORTCHILDID FROM ZsRTTransportChildDetail GROUP BY ZSRTTRANSPORTCHILDID) tcd ON tcd.ZSRTTRANSPORTCHILDID =tc.ZSRTTRANSPORTCHILDID
                      </string>.Value)
        sb.AppendFormat("WHERE  ep.CONTAINERNO IN('{0}') AND TransportType='{1}'", para("ContainerNo").Replace(",", "','"), para("TransportType"))
        If para.ContainsKey("workflowstepid") Then
            If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                sb.AppendFormat(" and rt.WORKFLOWSTEPID IN('{0}') ", para("workflowstepid").Replace(",", "','"))
            End If
        End If
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' 插入4.43	所需工装或辅料配送表
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertZsRequiredToolsTransport(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim Arr As New ArrayList()
        Dim sb As New StringBuilder
        sb.AppendLine("INSERT INTO ZsRequiredToolsTransport(ZSREQUIREDTOOLSTRANSPORTID,ENGINEPARTPLANID,WORKFLOWSTEPID,CREATEDATE,CREATEEMPLOYEEID,ISUSE,TRANSPORTTYPE,PURPOSE,APPLYTYPE,ProductBatchNo,STATUS)")
        sb.AppendFormat("VALUES('{0}','{1}','{2}',sysdate,'{3}','0','{4}','{5}','{6}','{7}','{8}') ", para("ZSREQUIREDTOOLSTRANSPORTID"), para("ENGINEPARTPLANID"), para("WORKFLOWSTEPID"), para("CREATEEMPLOYEEID"), para("TRANSPORTTYPE"), para("PURPOSE"), para("APPLYTYPE"), para("ProductBatchNo"), para("STATUS"))
        Arr.Add(sb.ToString)
        sb = New StringBuilder
        sb.AppendLine("INSERT INTO  ZsRTTransportChild(ZsRequiredToolsTransportID,ZsRTTransportChildID,ToolID,Qty)")
        sb.AppendFormat("VALUES('{0}','{1}','{2}','{3}')", para("ZSREQUIREDTOOLSTRANSPORTID"), para("ZsRTTransportChildID"), para("ToolID"), para("Qty"))
        Arr.Add(sb.ToString)
        Return DatabaseHelper.ExecuteSqlListTran(Arr)
    End Function
    ''' <summary>
    ''' 更新--工装或辅料配送表状态
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateZsRequiredToolsTransport(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim sb As New StringBuilder
        sb.AppendLine("UPDATE ZsRequiredToolsTransport SET ")
        If para.ContainsKey("RECEIVEEMPID") Then '接收
            sb.AppendFormat("STATUS='5',RECEIVEDATE=sysdate,RECEIVEEMPID='{0}'", para("RECEIVEEMPID"))
        End If
        If para.ContainsKey("STATUS") Then '更新其他状态
            sb.AppendFormat("STATUS='{0}' ", para("STATUS"))
        End If
        sb.AppendFormat("WHERE ENGINEPARTPLANID='{0}' AND workflowstepid='{1}' AND TransportType='{2}'", para("ENGINEPARTPLANID"), para("workflowstepid"), para("TransportType"))
        Return DatabaseHelper.ExecuteSql(sb.ToString) > 0
    End Function

    Function BatchUpdateZsRequiredToolsTransport(ByVal dt As DataTable, ByVal para As Dictionary(Of String, String)) As Boolean
        Dim arr As New ArrayList
        For i As Integer = 0 To dt.Rows.Count - 1
            Dim sb As New StringBuilder
            sb.AppendLine("UPDATE ZsRequiredToolsTransport SET ")
            sb.AppendFormat("STATUS='5',RECEIVEDATE=sysdate,RECEIVEEMPID='{0}'", para("RECEIVEEMPID"))
            sb.AppendFormat("WHERE ENGINEPARTPLANID='{0}' AND workflowstepid='{1}' AND TransportType='{2}'", dt.Rows(i)("ENGINEPARTPLANID").ToString, dt.Rows(i)("workflowstepid").ToString, para("TransportType"))
            arr.Add(sb.ToString)
        Next
        Return DatabaseHelper.ExecuteSqlListTran(arr)
    End Function
    ''' <summary>
    ''' 批量更新保存ERP保存数量、批次号
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function BatchUpdateZsRequiredToolsTransportPrepareQty(ByVal dt As DataTable) As Boolean
        Dim arr As New ArrayList
        For i As Integer = 0 To dt.Rows.Count - 1
            Dim sb As New StringBuilder
            sb.AppendLine("UPDATE ZsRequiredToolsTransport SET ")
            sb.AppendFormat("PREPAREQTY='{0}',OUTSTORETIME=sysdate,PRODUCTBATCHNO='{1}'", dt.Rows(i)("PREPAREQTY"), dt.Rows(i)("PRODUCTBATCHNO"))
            sb.AppendFormat("WHERE ZSREQUIREDTOOLSTRANSPORTID='{0}' ", dt.Rows(i)("ZSREQUIREDTOOLSTRANSPORTID").ToString)
            arr.Add(sb.ToString)
        Next
        Return DatabaseHelper.ExecuteSqlListTran(arr)
    End Function


    ''' <summary>
    ''' 插入--4.45	所需工装或辅料配送详细(配送中新->现场)
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertZsRTTransportChildDetail(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim Arr As New ArrayList()
        Dim sb As New StringBuilder
        sb.AppendLine("INSERT INTO ZsRTTransportChildDetail(ZsRequiredToolsTransportID,ZsRTTransportChildDetailID,ZsRTTransportChildID,ConfirmQty,CREATEDATE,CREATEEMPLOYEEID)")
        sb.AppendFormat("VALUES('{0}','{1}','{2}','{3}',sysdate,'{4}') ", para("ZsRequiredToolsTransportID"), para("ZsRTTransportChildDetailID"), para("ZsRTTransportChildID"), para("ConfirmQty"), para("CREATEEMPLOYEEID"))
        Arr.Add(sb.ToString)
        Return DatabaseHelper.ExecuteSqlListTran(Arr)
    End Function
    ''' <summary>
    ''' 插入---4.46	所需工装或辅料收料表（现场
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function InsertZsRTReceiveDetail(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim Arr As New ArrayList()
        Dim sb As New StringBuilder
        sb.AppendLine("INSERT INTO ZsRTReceiveDetail(ZsRequiredToolsTransportID,ZsRTReceiveDetailID,ZsRTTransportChildID,ReceiveQty,CREATEDATE,CREATEEMPLOYEEID)")
        sb.AppendFormat("VALUES('{0}','{1}','{2}','{3}',sysdate,'{4}') ", para("ZsRequiredToolsTransportID"), para("ZsRTReceiveDetailID"), para("ZsRTTransportChildID"), para("ReceiveQty"), para("CREATEEMPLOYEEID"))
        Arr.Add(sb.ToString)
        Return DatabaseHelper.ExecuteSqlListTran(Arr)
    End Function
    ''' <summary>
    ''' 检验所需--工装或辅材是否已全部配送完
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckeToolsSended(ByVal para As Dictionary(Of String, String)) As Boolean
        Dim sb As New StringBuilder()
        sb.AppendLine(<string>
                     SELECT *  FROM ZsRequiredToolsTransport rt
                      LEFT JOIN ZsRTTransportChild tc ON tc.ZSREQUIREDTOOLSTRANSPORTID =rt.ZSREQUIREDTOOLSTRANSPORTID
                      LEFT JOIN (SELECT SUM(CONFIRMQTY) CONFIRMQTY,ZSRTTRANSPORTCHILDID FROM ZsRTTransportChildDetail GROUP BY ZSRTTRANSPORTCHILDID) tcd ON tcd.ZSRTTRANSPORTCHILDID =tc.ZSRTTRANSPORTCHILDID
                      WHERE tc.QTY-NVL(tcd.CONFIRMQTY,0)>0
                      </string>)
        sb.AppendFormat(" AND ENGINEPARTPLANID='{0}' AND workflowstepid='{1}' AND TransportType='{2}'", para("EnginepartplanID"), para("workflowstepid"), para("TransportType"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Dim Status As String = "15"
        If (dt.Rows.Count > 0) Then
            Status = "10"
        End If
        sb = New StringBuilder()
        sb.AppendFormat(" UPDATE ZsRequiredToolsTransport SET Status='{0}' WHERE EnginepartplanID='{1}' AND APPLYTYPE='{2}' AND WORKFLOWSTEPID='{3}' AND TransportType='{4}'", Status, para("EnginepartplanID"), para("APPLYTYPE"), para("workflowstepid"), para("TransportType"))
        Return DatabaseHelper.ExecuteSql(sb.ToString) > 0
    End Function
    ''' <summary>
    ''' 查询--需要配送的辅材信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetAuxMaterialData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder()
        sb.AppendLine(<string>
                         SELECT rt.ZSREQUIREDTOOLSTRANSPORTID,to_Char(rt.CREATEDATE,'yyyy-MM-dd hh24:mm:ss') CREATEDATE,e.FULLNAME
                        ,tc.ZSRTTRANSPORTCHILDID,tc.QTY,decode(rt.STATUS,0,'待接收',5,'已接收',10,'配送中',15,'配送完',20,'收料完成','')STATUS,DECODE(rt.ApplyType,1,'正常','临时') ApplyType
                        ,rt.PURPOSE,DECODE(a.AUXMATERIALNAME,NULL,np.MATERIALCODE) AUXMATERIALNAME,DECODE(a.DESCRIPTION,NULL,np.MATERIALDESC,a.DESCRIPTION) DESCRIPTION,a.SPECIFICATION,r.RESOURCETYPENAME
                        ,tcd.CONFIRMQTY SENDQTY,NVL(tc.QTY,0)-NVL(tcd.CONFIRMQTY,0) EDITORQTY,rt.PRODUCTBATCHNO,rt.PREPAREQTY,to_char(rt.OUTSTORETIME,'yyyy-MM-dd hh24:mi') OUTSTORETIME 
                        FROM ZsRequiredToolsTransport rt
                        LEFT JOIN ZsRTTransportChild tc ON tc.ZSREQUIREDTOOLSTRANSPORTID =rt.ZSREQUIREDTOOLSTRANSPORTID
                        LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =rt.CREATEEMPLOYEEID 
                        LEFT JOIN AUXMATERIAL a ON a.AUXMATERIALID =tc.TOOLID
                        LEFT JOIN RESOURCETYPE r ON r.RESOURCETYPEID =a.RESOURCETYPEID 
                        LEFT JOIN ENGINEPARTPLAN ep ON ep.ENGINEPARTPLANID =rt.ENGINEPARTPLANID
                        LEFT JOIN ZsRTTransportChildDetail tcd ON tcd.ZSRTTRANSPORTCHILDID =tc.ZSRTTRANSPORTCHILDID
                        LEFT JOIN ZsNotProductMaterial np ON np.MATERIALCODE =tc.TOOLID 
                        WHERE 1=1
                      </string>.Value)
        'sb.AppendFormat("WHERE rt.WORKFLOWSTEPID='{0}' AND ep.CONTAINERNO ='{1}' AND rt.TransportType='{2}'", para("workflowstepid"), para("containerNo"), para("TransportType"))
        If para.ContainsKey("containerNo") Then
            If Not String.IsNullOrWhiteSpace(para("containerNo")) Then
                sb.AppendFormat(" and ep.CONTAINERNO='{0}'", para("containerNo"))
            End If
        End If
        If para.ContainsKey("workflowstepid") Then
            If Not String.IsNullOrWhiteSpace(para("workflowstepid")) Then
                sb.AppendFormat(" AND rt.WORKFLOWSTEPID='{0}' ", para("workflowstepid"))
            End If
        End If
        If para.ContainsKey("TransportType") Then
            If Not String.IsNullOrWhiteSpace(para("TransportType")) Then
                sb.AppendFormat(" and rt.TransportType='{0}' ", para("TransportType"))
            End If
        End If
        If para.ContainsKey("ID") Then
            If Not String.IsNullOrWhiteSpace(para("ID")) Then
                sb.AppendFormat(" and rt.ZSREQUIREDTOOLSTRANSPORTID='{0}' ", para("ID"))
            End If
        End If
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 工步/工步下--用到的--辅材信息--TransportType='2'
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkFlowStepAuxMaterialData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sql As New StringBuilder()
        sql.AppendLine(<string>
                    SELECT rtt.ZSREQUIREDTOOLSTRANSPORTID, rtt.WORKFLOWSTEPID,ttc.TOOLID,e.CONTAINERNO FROM ZsRequiredToolsTransport rtt
					LEFT JOIN ZsRTTransportChild ttc ON ttc.ZSREQUIREDTOOLSTRANSPORTID =rtt.ZSREQUIREDTOOLSTRANSPORTID
					LEFT JOIN ENGINEPARTPLAN e ON e.ENGINEPARTPLANID =rtt.ENGINEPARTPLANID 
					WHERE rtt.ApplyType='1' AND rtt.TransportType='2'
                       </string>)
        sql.AppendFormat(" AND rtt.WORKFLOWSTEPID IN('{0}') AND e.ContainerNo='{1}'", para("workflowstepid").Replace(",", "','"), para("ContainerNo"))
        Dim sb As New StringBuilder()
        '工步下--用到的--辅材信息
        sb.AppendLine(<string>
                 SELECT  w.WORKFLOWSTEPID,w.WORKFLOWSTEPNAME,a.AUXMATERIALNAME,a.DESCRIPTION,a.SPECIFICATION ,r.RESOURCETYPENAME,s.QTY,s."SEQUENCE"
                ,DECODE(rtt.ZSREQUIREDTOOLSTRANSPORTID ,NULL,'未发送','已发送') STATUS,a.AUXMATERIALID TOOLSID
                FROM STEPINFOTOOLS s  
                LEFT JOIN AUXMATERIAL a ON a.AUXMATERIALID =s.TOOLS 
                LEFT JOIN RESOURCETYPE r ON r.RESOURCETYPEID =a.RESOURCETYPEID 
                LEFT JOIN STEPINFO s2 ON s2.STEPINFOID =s.STEPINFOID 
                LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =s2.WORKFLOWSTEPID
                      </string>.Value)
        sb.AppendFormat("LEFT JOIN ({0}) rtt ON rtt.WORKFLOWSTEPID=s2.WORKFLOWSTEPID AND rtt.TOOLID=s.TOOLS ", sql.ToString)
        sb.AppendFormat("WHERE w.WORKFLOWSTEPID IN('{0}') AND a.AUXMATERIALID IS NOT NULL", para("workflowstepid").Replace(",", "','"))
        '工序下--用到的--辅材信息
        sb.AppendLine(<string>
                UNION ALL
                SELECT w.WORKFLOWSTEPID,w2.WORKFLOWSTEPNAME,a.AUXMATERIALNAME,a.DESCRIPTION,a.SPECIFICATION ,r.RESOURCETYPENAME,w.QTY,w."SEQUENCE" 
                ,DECODE(rtt.ZSREQUIREDTOOLSTRANSPORTID ,NULL,'未发送','已发送')STATUS,a.AUXMATERIALID TOOLSID
                FROM WORKFLOWSTEPTOOLS w
                LEFT JOIN AUXMATERIAL a ON a.AUXMATERIALID =w.TOOLS 
                LEFT JOIN RESOURCETYPE r ON r.RESOURCETYPEID =a.RESOURCETYPEID 
                LEFT JOIN WORKFLOWSTEP w2 ON w2.WORKFLOWSTEPID =w.WORKFLOWSTEPID 
                      </string>.Value)
        sb.AppendFormat("LEFT JOIN ({0}) rtt ON rtt.WORKFLOWSTEPID=w.WORKFLOWSTEPID AND rtt.TOOLID=w.TOOLS ", sql.ToString)
        sb.AppendFormat("WHERE w2.WORKFLOWSTEPID IN('{0}') AND a.AUXMATERIALID IS NOT NULL", para("workflowstepid").Replace(",", "','"))
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 工步/工序下--用到的--工装工具信息--TransportType='1'
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkFlowStepToolsData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sql As New StringBuilder()
        sql.AppendLine(<string>
                    SELECT rtt.ZSREQUIREDTOOLSTRANSPORTID, rtt.WORKFLOWSTEPID,ttc.TOOLID,e.CONTAINERNO FROM ZsRequiredToolsTransport rtt
					LEFT JOIN ZsRTTransportChild ttc ON ttc.ZSREQUIREDTOOLSTRANSPORTID =rtt.ZSREQUIREDTOOLSTRANSPORTID
					LEFT JOIN ENGINEPARTPLAN e ON e.ENGINEPARTPLANID =rtt.ENGINEPARTPLANID 
					WHERE rtt.ApplyType='1' AND rtt.TransportType='1'
                       </string>)
        sql.AppendFormat(" AND rtt.WORKFLOWSTEPID IN('{0}') AND e.ContainerNo='{1}'", para("workflowstepid").Replace(",", "','"), para("ContainerNo"))
        Dim sb As New StringBuilder()
        '工步下--用到的--工装工具信息
        sb.AppendLine(<string>
                SELECT w.WORKFLOWSTEPID,w.WORKFLOWSTEPNAME,a.NAME,a.DESCRIPTION,a.SPECIFICATION ,r.RESOURCETYPENAME,s.QTY,s."SEQUENCE"
                ,DECODE(rtt.ZSREQUIREDTOOLSTRANSPORTID ,NULL,'未发送','已发送')STATUS,a.TOOLSID
                FROM STEPINFOTOOLS s  
                LEFT JOIN (
	                SELECT CUTTOOLID TOOLSID,CUTTOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION,SPECIFICATION,RESOURCETYPEID FROM CUTTOOL 
	                UNION ALL SELECT SPECIALTOOLID TOOLSID,SPECIALTOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION,SPECIFICATION,RESOURCETYPEID FROM SPECIALTOOL   
	                UNION ALL SELECT MEASURETOOLID TOOLSID,MEASURETOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION,SPECIFICATION,RESOURCETYPEID FROM MEASURETOOL
                ) a ON a.TOOLSID =s.TOOLS 
                LEFT JOIN RESOURCETYPE r ON r.RESOURCETYPEID =a.RESOURCETYPEID 
                LEFT JOIN STEPINFO s2 ON s2.STEPINFOID =s.STEPINFOID 
                LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =s2.WORKFLOWSTEPID
                      </string>.Value)
        sb.AppendFormat("LEFT JOIN ({0}) rtt ON rtt.WORKFLOWSTEPID=s2.WORKFLOWSTEPID AND rtt.TOOLID=s.TOOLS ", sql.ToString)
        sb.AppendFormat("WHERE w.WORKFLOWSTEPID IN('{0}') AND a.TOOLSID IS NOT NULL", para("workflowstepid").Replace(",", "','"))
        '工序下--用到的--工装工具信息
        sb.AppendLine(<string>
                UNION ALL
                SELECT w.WORKFLOWSTEPID,w2.WORKFLOWSTEPNAME,a.NAME,a.DESCRIPTION,a.SPECIFICATION ,r.RESOURCETYPENAME,w.QTY,w."SEQUENCE" 
                ,DECODE(rtt.ZSREQUIREDTOOLSTRANSPORTID ,NULL,'未发送','已发送')STATUS,a.TOOLSID
                FROM WORKFLOWSTEPTOOLS w
                LEFT JOIN (
	                SELECT CUTTOOLID TOOLSID,CUTTOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION,SPECIFICATION,RESOURCETYPEID FROM CUTTOOL 
	                UNION ALL SELECT SPECIALTOOLID TOOLSID,SPECIALTOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION,SPECIFICATION,RESOURCETYPEID FROM SPECIALTOOL   
	                UNION ALL SELECT MEASURETOOLID TOOLSID,MEASURETOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION,SPECIFICATION,RESOURCETYPEID FROM MEASURETOOL
                ) a ON a.TOOLSID =w.TOOLS 
                LEFT JOIN RESOURCETYPE r ON r.RESOURCETYPEID =a.RESOURCETYPEID 
                LEFT JOIN WORKFLOWSTEP w2 ON w2.WORKFLOWSTEPID =w.WORKFLOWSTEPID 
                      </string>.Value)
        sb.AppendFormat("LEFT JOIN ({0}) rtt ON rtt.WORKFLOWSTEPID=w.WORKFLOWSTEPID AND rtt.TOOLID=w.TOOLS ", sql.ToString)
        sb.AppendFormat("WHERE w2.WORKFLOWSTEPID IN('{0}') AND a.TOOLSID IS NOT NULL", para("workflowstepid").Replace(",", "','"))
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 辅材、工装工具--配送、接收信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetRequiredToolsTransportConfirmData(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder()
        '辅材信息
        sb.AppendLine(<string>
                   SELECT a.AUXMATERIALNAME NAME,a.DESCRIPTION,a.SPECIFICATION,r.RESOURCETYPENAME,rt.TransportType 
                  ,tc.QTY ,tcd.CONFIRMQTY,rtd.RECEIVEQTY,tc.ZSREQUIREDTOOLSTRANSPORTID,tc.ZSRTTRANSPORTCHILDID ,nvl(tcd.CONFIRMQTY,0)-nvl(rtd.RECEIVEQTY,0) NNORECEIVEQTY 
                  FROM ZsRequiredToolsTransport rt   
                  LEFT JOIN ZsRTTransportChild tc ON tc.ZSREQUIREDTOOLSTRANSPORTID =rt.ZSREQUIREDTOOLSTRANSPORTID
                  LEFT JOIN (SELECT SUM(CONFIRMQTY)CONFIRMQTY,ZSRTTRANSPORTCHILDID FROM  ZsRTTransportChildDetail GROUP BY ZSRTTRANSPORTCHILDID) tcd ON tcd.ZSRTTRANSPORTCHILDID =tc.ZSRTTRANSPORTCHILDID
                  LEFT JOIN (SELECT SUM(RECEIVEQTY)RECEIVEQTY,ZsRequiredToolsTransportID,ZsRTTransportChildID FROM  ZsRTReceiveDetail
  			                GROUP BY ZsRequiredToolsTransportID,ZsRTTransportChildID
                  ) rtd ON rtd.ZsRequiredToolsTransportID=rt.ZSREQUIREDTOOLSTRANSPORTID AND rtd.ZsRTTransportChildID=tc.ZSRTTRANSPORTCHILDID
                  LEFT JOIN AUXMATERIAL a ON a.AUXMATERIALID =tc.TOOLID
                  LEFT JOIN RESOURCETYPE r ON r.RESOURCETYPEID =a.RESOURCETYPEID
                  LEFT JOIN ENGINEPARTPLAN ep ON ep.ENGINEPARTPLANID =rt.ENGINEPARTPLANID
                  WHERE rt.ApplyType='1' AND rt.TransportType='2' 
                      </string>)
        sb.AppendFormat(" AND rt.WORKFLOWSTEPID='{0}' AND ep.ContainerNo='{1}'", para("workflowstepid"), para("ContainerNo"))

        '工装工具信息
        sb.AppendLine(<string>
               UNION ALL SELECT a.NAME,a.DESCRIPTION,a.SPECIFICATION,r.RESOURCETYPENAME,rt.TransportType 
                  ,tc.QTY ,tcd.CONFIRMQTY,rtd.RECEIVEQTY,tc.ZSREQUIREDTOOLSTRANSPORTID,tc.ZSRTTRANSPORTCHILDID,nvl(tcd.CONFIRMQTY,0)-nvl(rtd.RECEIVEQTY,0) NNORECEIVEQTY  
                  FROM ZsRequiredToolsTransport rt   
                  LEFT JOIN ZsRTTransportChild tc ON tc.ZSREQUIREDTOOLSTRANSPORTID =rt.ZSREQUIREDTOOLSTRANSPORTID
                  LEFT JOIN (SELECT SUM(CONFIRMQTY)CONFIRMQTY,ZSRTTRANSPORTCHILDID FROM  ZsRTTransportChildDetail GROUP BY ZSRTTRANSPORTCHILDID) tcd ON tcd.ZSRTTRANSPORTCHILDID =tc.ZSRTTRANSPORTCHILDID
                  LEFT JOIN (SELECT SUM(RECEIVEQTY)RECEIVEQTY,ZsRequiredToolsTransportID,ZsRTTransportChildID FROM  ZsRTReceiveDetail
  			                GROUP BY ZsRequiredToolsTransportID,ZsRTTransportChildID
                  ) rtd ON rtd.ZsRequiredToolsTransportID=rt.ZSREQUIREDTOOLSTRANSPORTID AND rtd.ZsRTTransportChildID=tc.ZSRTTRANSPORTCHILDID
                  LEFT JOIN (
  	                SELECT CUTTOOLID ID,CUTTOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION,SPECIFICATION,RESOURCETYPEID FROM CUTTOOL 
	                UNION ALL SELECT SPECIALTOOLID ID,SPECIALTOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION,SPECIFICATION,RESOURCETYPEID FROM SPECIALTOOL   
	                UNION ALL SELECT MEASURETOOLID ID,MEASURETOOLNAME||'('||DESCRIPTION||')' NAME,DESCRIPTION,SPECIFICATION,RESOURCETYPEID FROM MEASURETOOL
                  ) a ON a.ID =tc.TOOLID
                  LEFT JOIN RESOURCETYPE r ON r.RESOURCETYPEID =a.RESOURCETYPEID
                  LEFT JOIN ENGINEPARTPLAN ep ON ep.ENGINEPARTPLANID =rt.ENGINEPARTPLANID
                  WHERE rt.ApplyType='1' AND rt.TransportType='1'
                      </string>)
        sb.AppendFormat(" AND rt.WORKFLOWSTEPID='{0}' AND ep.ContainerNo='{1}'", para("workflowstepid"), para("ContainerNo"))

        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)

    End Function

    '--------------------------------------
    ''' <summary>
    ''' 工装工具现场配送查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetZsRequiredToolsALL(ByVal para As Dictionary(Of String, String)) As DataTable
        Dim sb As New StringBuilder
        sb.AppendLine(<string>
                  SELECT DISTINCT * FROM (SELECT u.attributevalue,mfg.mfgordername,ep.containerno togetherflag,decode(WF.WORKFLOWTYPE,'施工工艺','临时','技通工艺','临时','正常') WORKFLOWTYPE
                ,pb.productname,p.description, w.workflowstepname STEPNAME,wfs.workflowstepname,wf.description wfdesc,ep.containerno,
                to_char(ep.plannedstartdate,'yyyy-MM-dd hh24:mi') plannedstartdate, to_char(ep.plannedfinishdate,'yyyy-MM-dd hh24:mi') plannedfinishdate
                ,NVL(ep.containerid,ep.enginecontainerid) containerid,ep.containername,imt.WORKFLOWSTEPID,ep.ENGINEPARTPLANID
                FROM ZsRequiredToolsTransport imt
                LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =imt.WORKFLOWSTEPID
                LEFT JOIN enginepartplan ep ON ep.ENGINEPARTPLANID =imt.ENGINEPARTPLANID 
                LEFT JOIN mfgorder mfg ON mfg.mfgorderid=ep.mfgorderid 
                LEFT JOIN Userattribute u ON u.parentid=mfg.mfgorderid AND u.userattributename='发动机'
                LEFT JOIN workflow wf ON wf.workflowid=ep.workflowid
                LEFT JOIN workflowbase wfb on wfb.workflowbaseid=wf.workflowbaseid
                LEFT JOIN product p ON ep.productid=p.productid
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid WHERE 1=1
                      </string>)

        If para.ContainsKey("ContainerNo") Then
            If Not String.IsNullOrWhiteSpace(para("ContainerNo")) Then
                sb.AppendFormat(" and ep.ContainerNo like '%{0}%' ", para("ContainerNo"))
            End If
        End If

        If para.ContainsKey("MfgOrdername") Then
            If Not String.IsNullOrWhiteSpace(para("MfgOrdername")) Then
                sb.AppendFormat(" and mfg.mfgordername like '%{0}%' ", para("MfgOrdername"))
            End If
        End If

        If para.ContainsKey("Status") Then
            If Not String.IsNullOrWhiteSpace(para("Status")) Then
                sb.AppendFormat(" and imt.STATUS='{0}' ", para("Status"))
            End If
        End If
        If para.ContainsKey("TransportType") Then
            If Not String.IsNullOrWhiteSpace(para("TransportType")) Then
                sb.AppendFormat(" and imt.TransportType='{0}' ", para("TransportType"))
            End If
        End If
        sb.AppendLine(")Order by plannedfinishdate,mfgordername,productname ASC")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    ''' <summary>
    ''' 工装工具在制统计--查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetToolsInProcessCensus(ByVal para As Dictionary(Of String, String)) As uMESProcessResult
        Dim sb As New StringBuilder
        sb.AppendLine(<string>SELECT* FROM(  SELECT DECODE(cl.CUTTOOLNAME,NULL,DECODE(s.SPECIALTOOLNAME,NULL,m.MEASURETOOLNAME,s.SPECIALTOOLNAME),cl.CUTTOOLNAME) TOOlNAME
                        ,DECODE(cl.DRAWINGNO,NULL,DECODE(s.DRAWINGNO,NULL,m.DRAWINGNO,s.DRAWINGNO),cl.DRAWINGNO) DRAWINGNO
                        ,DECODE(cl.DESCRIPTION ,NULL,DECODE(s.DESCRIPTION,NULL,m.DESCRIPTION,s.DESCRIPTION),cl.DESCRIPTION) DESCRIPTION
                        ,DECODE(cl.SPECIFICATION,NULL,DECODE(s.SPECIFICATION,NULL,m.SPECIFICATION,s.SPECIFICATION),cl.SPECIFICATION) SPECIFICATION
                        ,DECODE(cl.CUTTOOLNAME,NULL,DECODE(s.SPECIALTOOLNAME,NULL,'夹模具','量具'),'刀具') TOOlTYPE
                        ,m2.MFGORDERNAME,u.attributevalue,SUBSTR(w.WORKFLOWSTEPNAME,0,INSTR(w.WORKFLOWSTEPNAME,'-',1)-1) SENO,
                        SUBSTR(w.WORKFLOWSTEPNAME,INSTR(w.WORKFLOWSTEPNAME,'-',1)+1) WORKFLOWSTEPNAME,T.TEAMNAME 
                        ,CSD.FULLNAME,to_char(sysdate,'yyyy-MM-dd') OUTSTORETIME,to_char(ADD_MONTHS(sysdate,1),'yyyy-MM-dd') BACKTIME,CSD.PLANNEDSTARTDATE,CSD.PLANNEDFINISHDATE</string>.Value)
        sb.AppendLine(",CASE WHEN to_char(ADD_MONTHS(sysdate,1),'yyyy-MM-dd')<=to_char(sysdate,'yyyy-MM-dd') THEN '是' ELSE '否' END ISBACK")
        sb.AppendLine(<string>FROM ZsRequiredToolsTransport rt
                        LEFT JOIN ZsRTTransportChild tc ON tc.ZSREQUIREDTOOLSTRANSPORTID =rt.ZSREQUIREDTOOLSTRANSPORTID
                        LEFT JOIN CUTTOOL cl ON cl.CUTTOOLID =tc.TOOLID 
                        LEFT JOIN SPECIALTOOL s ON s.SPECIALTOOLID =tc.TOOLID
                        LEFT JOIN MEASURETOOL m ON m.MEASURETOOLID=tc.TOOLID
                        LEFT JOIN ENGINEPARTPLAN ep ON ep.ENGINEPARTPLANID =rt.ENGINEPARTPLANID
                        LEFT JOIN MFGORDER m2 ON m2.MFGORDERID =ep.MFGORDERID 
                        LEFT JOIN Userattribute u ON u.parentid=m2.mfgorderid AND u.userattributename='发动机'
                        LEFT JOIN WORKFLOWSTEP w ON w.WORKFLOWSTEPID =rt.WORKFLOWSTEPID 
                        LEFT JOIN TEAM T ON T.TEAMID = W.TEAMID
                        LEFT JOIN (
	                        SELECT e.FULLNAME,CSD.WORKFLOWSTEPID,to_char(CSD.PLANNEDSTARTDATE,'yyyy-MM-dd hh24:mm') PLANNEDSTARTDATE,to_char(CSD.PLANNEDFINISHDATE,'yyyy-MM-dd hh24:mm') PLANNEDFINISHDATE,CSD.CONTAINERID FROM CONTAINERSTEPDISPATCH CSD
							LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDD ON CSDD.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID
							LEFT JOIN EMPLOYEE e ON e.EMPLOYEEID =CSDD.WORKERID 
							WHERE CSD.ISUSE=0
                        ) CSD ON CSD.WORKFLOWSTEPID =w.WORKFLOWSTEPID AND CSD.CONTAINERID=ep.CONTAINERID) WHERE 1=1  </string>.Value)
        If para.ContainsKey("Name") Then
            If Not String.IsNullOrWhiteSpace(para("Name")) Then
                sb.AppendFormat(" and TOOlNAME like '%{0}%' ", para("Name"))
            End If
        End If
        If para.ContainsKey("DrawingNo") Then
            If Not String.IsNullOrWhiteSpace(para("DrawingNo")) Then
                sb.AppendFormat(" and DRAWINGNO like '%{0}%' ", para("DrawingNo"))
            End If
        End If
        If para.ContainsKey("startedate") Then
            If Not String.IsNullOrWhiteSpace(para("startedate")) Then
                sb.AppendFormat(" and OUTSTORETIME>='{0}' ", para("startedate"))
            End If
        End If
        If para.ContainsKey("enddate") Then
            If Not String.IsNullOrWhiteSpace(para("enddate")) Then
                sb.AppendFormat(" and OUTSTORETIME<='{0}' ", para("enddate"))
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function


End Class
